from data_access.read_db import execute_sql, execute_select

def main():
    # Define the reverse mapping dictionary (abbreviation -> full word)
    full_word_mapping = {
        'RD': 'ROAD',
        'AVE': 'AVENUE',
        'BT': 'BUKIT',
        'BLVD': 'BOULEVARD',
        'CL': 'CLOSE',
        'CTRL': 'CENTRAL',
        'CRES': 'CRESCENT',
        'DR': 'DRIVE',
        'JLN': 'JALAN',
        'KG': 'KAMPONG',
        'LN': 'LANE',
        'LOR': 'LORONG',
        'UPP': 'UPPER',
        'PL': 'PLACE',
        'ST': 'STREET',
        'TG': 'TANJONG',
        'NTH': 'NORTH',
        'STH': 'SOUTH',
        'CTR': 'CENTRE'
    }

    # First, fetch all records that need updating
    records_df = execute_select("""
        SELECT month, planarea, flat_type, blk_no, street, storey_range, 
               floor_area_sqm, flat_model, lease_commence_date, resale_price 
        FROM resale_flat_prices
    """)

    total_records = len(records_df)
    print(f"Total records to process: {total_records}")

    # Process each record and update if needed
    for index, record in records_df.iterrows():
        # Show progress every 1000 records
        if index % 10 == 0 or index == total_records - 1:
            print(f"Processing record {index + 1} of {total_records} ({((index + 1) / total_records) * 100:.1f}%)")

        # Access fields using dictionary-style access or dot notation
        month = record['month']
        planarea = record['planarea']
        flat_type = record['flat_type']
        blk_no = record['blk_no']
        street = record['street']
        storey_range = record['storey_range']
        floor_area_sqm = record['floor_area_sqm']
        flat_model = record['flat_model']
        lease_commence_date = record['lease_commence_date']
        resale_price = record['resale_price']

        original_street = street

        # Skip if street is None or not a string
        if not isinstance(street, str):
            continue

        # Split the street into parts and process each part
        parts = street.split()
        updated_parts = []

        for part in parts:
            # Convert to uppercase for comparison
            upper_part = part.upper()
            # Check if the part is an abbreviation we need to expand
            if upper_part in full_word_mapping:
                # Get the full word and apply original capitalization
                full_word = full_word_mapping[upper_part]
                # Preserve the original capitalization if it was all caps
                if part == upper_part:
                    updated_parts.append(full_word)
                else:
                    updated_parts.append(full_word.capitalize())
            else:
                updated_parts.append(part)

        updated_street = ' '.join(updated_parts)

        # Only update if the street name actually changed
        if updated_street != original_street:
            print(f"Updating record {index + 1}: {original_street} -> {updated_street}")
            # Create the SQL string with values filled in
            sql = f"""
                UPDATE resale_flat_prices 
                SET street = '{updated_street.replace("'", "''")}' 
                WHERE month = '{month}' 
                  AND planarea = '{planarea.replace("'", "''")}' 
                  AND flat_type = '{flat_type.replace("'", "''")}' 
                  AND blk_no = '{blk_no.replace("'", "''")}' 
                  AND street = '{original_street.replace("'", "''")}' 
                  AND storey_range = '{storey_range.replace("'", "''")}' 
                  AND floor_area_sqm = {floor_area_sqm} 
                  AND flat_model = '{flat_model.replace("'", "''")}' 
                  AND lease_commence_date = {lease_commence_date} 
                  AND resale_price = {resale_price}
                """
            execute_sql(sql)

    print("Processing complete!")


